--    Author    : ChenErHao
--    Name      : DM.T_MTH_DEP_BALANCE_GROWTH_TOTAL.HQL
--    Functions : 增速-存款余额增速
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-24  ChenErHao       1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE DM.T_MTH_DEP_BALANCE_GROWTH_TOTAL  PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
SELECT
    T1.ORG_ID,
    T1.CUSTOMER_TYPE,    
    T1.PRODUCT_CATEGORY,    
    T1.DEP_PERIOD,
    T1.AREA_NO_ID,
    nvl(sum(case when DATA_DATE='#V_DATA_DATE#' then T1.DEP_BALANCE end),0) DEP_BALANCE,
    nvl(sum(case when DATA_DATE='#V_LAST_1M_END_DATE#' then T1.DEP_BALANCE end),0) LAST_MTH_DEP_BALANCE,
    nvl(sum(case when DATA_DATE='#V_LAST_12M_END_DATE#' then T1.DEP_BALANCE end),0) LAST_YEAR_DEP_BALANCE,
    nvl(sum(case when DATA_DATE='#V_DATA_DATE#' then T1.DEP_BALANCE end),0) YEAR_END_DEP_BALANCE,
    nvl(sum(case when DATA_DATE='#V_LAST_1Y_END_DAY#' then T1.DEP_BALANCE end),0) LAST_YEAR_END_DEP_BALANCE,
    nvl(sum(case when DATA_DATE='#V_DATA_DATE#' then T1.WEIGHT_AMT end),0) WEIGHT_AMT
FROM
    (
        SELECT
            A.DATA_DATE,
            BELONG_AREA_CODE   AREA_NO_ID,            
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END ORG_ID,
            CUSTOMER_TYPE         CUSTOMER_TYPE, 
            CASE WHEN C.DEPB_CATEGORY_ID_1 IS NULL THEN '9999'
                 WHEN C.DEPB_CATEGORY_ID_1 ='' THEN '9999'
                 ELSE C.DEPB_CATEGORY_ID_1 END PRODUCT_CATEGORY,
            A.LOAN_PERIOD_TYPE_ID            DEP_PERIOD,
            SUM(DEP_BALANCE)      DEP_BALANCE, 
            SUM(LOAN_OCCURRENCE_LEVEL) WEIGHT_AMT
        FROM
            (SELECT DATA_DATE,CUSTOMER_TYPE,LOAN_PERIOD_TYPE_ID,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,DEP_BALANCE,LOAN_OCCURRENCE_LEVEL
            FROM EDW.DEPB
        WHERE
            DATA_DATE in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#','#V_LAST_1Y_END_DAY#')) A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE end = B.ORG_ID
        LEFT JOIN DMCODE.T_DEPB_CATEGORY C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY end= C.DEPB_CATEGORY_ID 
        GROUP BY
            A.DATA_DATE ,
            A.BELONG_AREA_CODE,
            B.ORG_LVL_ID_2,            
            C.DEPB_CATEGORY_ID_1,        
            A.CUSTOMER_TYPE,
            A.LOAN_PERIOD_TYPE_ID) T1
GROUP BY
T1.ORG_ID,
    T1.CUSTOMER_TYPE,    
    T1.PRODUCT_CATEGORY,    
    T1.DEP_PERIOD,
    T1.AREA_NO_ID 
;